package edu.psu.collegerecommendationtool.coreservlets;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
///import java.sql.Statement;

import javax.faces.bean.*;

import edu.psu.collegerecommendationtool.commonutils.MySQLInfo;

@ManagedBean(name="registrationBean")
public class RegistrationController {
   private String entered_username;
   private String entered_password;
   private String entered_firstname;
   private String entered_lastname;
   private String entered_email;

   public String getEntered_username() {
      return entered_username;
   }
   public void setEntered_username(String entered_username) {
      this.entered_username = entered_username;
   }

   public String getEntered_password() {
      return entered_password;
   }
   public void setEntered_password(String entered_password) {
      this.entered_password = entered_password;
   }

   public String getEntered_firstname() {
      return entered_firstname;
   }
   public void setEntered_firstname(String entered_firstname) {
      this.entered_firstname = entered_firstname;
   }

   public String getEntered_lastname() {
      return entered_lastname;
   }
   public void setEntered_lastname(String entered_lastname) {
      this.entered_lastname = entered_lastname;
   }

   public String getEntered_email() {
      return entered_email;
   }
   public void setEntered_email(String entered_email) {
      this.entered_email = entered_email;
   }

////////////////////////////////////////////////////////////////////////

   public String registrationAction() throws ClassNotFoundException {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

      Class.forName("com.mysql.jdbc.Driver");

      String sql = "SELECT * FROM " + mysqlinfo.getDbName() + "." + mysqlinfo.getAcctable() + 
                   " WHERE  username = '" + entered_username + "';";
      boolean usernameFree = false;

      usernameFree = usernameTaken(sql, DB_URL);

      if (true == usernameFree) {
System.out.println("Attempt Inserts");
    	 sql = "INSERT INTO " + mysqlinfo.getDbName() + "." + mysqlinfo.getAcctable() + 
                " (username, password, firstname, lastname, email) VALUES ('" + 
                 entered_username + "', '" + entered_password + "', '" +
                 entered_firstname + "', '" + entered_lastname + "', '" +
                 entered_email + "');";
         createAccount(sql, DB_URL);

         sql = "INSERT INTO " + mysqlinfo.getDbName() + "." + mysqlinfo.getDemtable() + 
                 " (state) VALUES ('SELECT STATE')";
         createAccount(sql, DB_URL);

         sql = "INSERT INTO " + mysqlinfo.getDbName() + "." + mysqlinfo.getEdutable() + 
                 " (GPA) VALUES ('0.0')";
         createAccount(sql, DB_URL);

         sql = "INSERT INTO " + mysqlinfo.getDbName() + "." + mysqlinfo.getPretable() + 
                 " (location) VALUES ('SELECT PREFERENCE')";
         createAccount(sql, DB_URL);

         sql = "INSERT INTO " + mysqlinfo.getDbName() + "." + mysqlinfo.getRestable() + 
                 " (active) VALUES ('0')";
         createAccount(sql, DB_URL);


         return ("RegistrationSuccessPage");
      }
      else {
          return ("RegistrationFailurePage");
      }

   }

////////////////////////////////////////////////////////////////////////

   public boolean usernameTaken(String sql, String DB_URL) {
	  String un = "";
      boolean usernameFree = false;

      ResultSet result = null;
      PreparedStatement pst = null;

      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            un = result.getString(2);
         }

         if (un.equals(entered_username)) {
            usernameFree = false;
         }
         else {
             usernameFree = true;
         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }
System.out.println(usernameFree);
      return(usernameFree);
   }

////////////////////////////////////////////////////////////////////////

   public void createAccount(String sql, String DB_URL) {
      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

////////////////////////////////////////////////////////////////////////

   public Connection getConnection(String DB_URL) {
      Connection con = null;

      try {
         con = DriverManager.getConnection(DB_URL);
         System.out.println("Connection completed.");
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      finally { }

      return con;
   }

////////////////////////////////////////////////////////////////////////

   public String returnLogin() {
	      return("LoginPage");
   }

////////////////////////////////////////////////////////////////////////

   public String returnRegistration() {
	      return("RegistrationPage");
   }

}